package com.ailanyin.updatesql.service.impl;

import com.ailanyin.updatesql.model.TableInfo;
import com.ailanyin.updatesql.service.UpdateSqlService;
import com.ailanyin.updatesql.utils.DbUtil;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
 * UpdateSqlServiceImpl
 *
 * @author ailanyin
 */
public class UpdateSqlServiceImpl implements UpdateSqlService {

    @Override
    public void startUpdate(String filepath) {
        // 1. 查数据库是否有日志表
        if (!checkIsHasLogTables()) {
            // 创建日志表
            createLogTable();
        }
        // 2. 读取日志表内容
        List<TableInfo> updateLogList = getAllUpdateLog();
        // 3. 读取所有sql文件
        Resource[] allSqlFile = getAllSqlFile(filepath);
        // 分类sql文件 表名:[allVersion]
        Map<String, List<Integer>> sqlNameAndAllVersion = groupTable(allSqlFile);
        // 4. 对比,分出新增/更新的表
        Map<String, List<TableInfo>> groupTableMap = groupTable(updateLogList, sqlNameAndAllVersion);
        List<TableInfo> addTableList = groupTableMap.get("addTableList");
        List<TableInfo> updateTableList = groupTableMap.get("updateTableList");
        // 5. 执行sql操作
        startHandleSql(addTableList, filepath, sqlNameAndAllVersion, false);
        startHandleSql(updateTableList, filepath, sqlNameAndAllVersion, true);
    }

    /**
     * 检查是否有日志表
     *
     * @return boolean
     */
    private boolean checkIsHasLogTables() {
        List<TableInfo> list = DbUtil.getList(TableInfo.class, "select table_name as tableName,table_version as tableVersion from ailanyin_updatesql_log limit 1;");
        return list != null;
    }

    /**
     * 获取更新日志
     *
     * @return list
     */
    private List<TableInfo> getAllUpdateLog() {
        return DbUtil.getList(TableInfo.class, "select table_name as tableName,table_version as tableVersion from ailanyin_updatesql_log;");
    }

    /**
     * 执行sql文件以及更新日志表
     *
     * @param tableList            需要新增的基础信息,包括表名 + 版本号
     * @param filepath             sql文件存在的路径
     * @param sqlNameAndAllVersion 表名 + 所有版本号
     * @param isUpdate             是否是新增
     */
    private void startHandleSql(List<TableInfo> tableList, String filepath, Map<String, List<Integer>> sqlNameAndAllVersion, boolean isUpdate) {
        for (TableInfo tableInfo : tableList) {
            String sqlFileName = tableInfo.getTableName() + "_" + tableInfo.getTableVersion() + ".sql";
            System.out.println("========== 正在处理: " + sqlFileName + " ==========");
            boolean result = false;
            try {
                result = runSqlScript(new PathMatchingResourcePatternResolver().getResources("classpath:" + filepath + "/" + sqlFileName)[0]);
            } catch (IOException e) {
                e.printStackTrace();
            }

            if (!result) {
                System.err.println("========== 处理失败: " + sqlFileName + " ==========");
                continue;
            }
            List<Integer> sqlVersions = sqlNameAndAllVersion.get(tableInfo.getTableName());
            // 取最后一个版本号
            Integer version = sqlVersions.get(sqlVersions.size() - 1);
            if (version.equals(tableInfo.getTableVersion())) {
                List<String> list = new ArrayList<>();
                if (isUpdate) {
                    // 更新
                    list.add("UPDATE `ailanyin_updatesql_log` SET `table_version` = " + version + " WHERE `table_name` = '" + tableInfo.getTableName() + "' LIMIT 1");
                } else {
                    // 新增
                    list.add("INSERT INTO `ailanyin_updatesql_log` (`table_name`, `table_version`) VALUES ('" + tableInfo.getTableName() + "', " + version + ")");
                }
                DbUtil.runHandleSql(list);
            }
        }

    }

    /**
     * 创建基础日志表,整个项目基于此表
     */
    private void createLogTable() {
        List<String> list = new ArrayList<>();
        list.add("SET NAMES utf8mb4;");
        list.add("SET FOREIGN_KEY_CHECKS = 0;");
        list.add("DROP TABLE IF EXISTS `ailanyin_updatesql_log`;");
        list.add("CREATE TABLE `ailanyin_updatesql_log`  (`table_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`table_version` int(11) NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;");
        list.add("SET FOREIGN_KEY_CHECKS = 1;");
        DbUtil.runHandleSql(list);
    }

    /**
     * 获取 filepath下的所有sql文件
     *
     * @param filepath filepath
     * @return Resource集合
     */
    private Resource[] getAllSqlFile(String filepath) {
        try {
            return new PathMatchingResourcePatternResolver().getResources("classpath:" + filepath + "/**.sql");
        } catch (IOException e) {
            return null;
        }
    }

    /**
     * 给文件名分组
     *
     * @param allSqlFile allSqlFile
     * @return Map<String, List < Integer>> String:表名 List<Integer>>:版本数组
     */
    private Map<String, List<Integer>> groupTable(Resource[] allSqlFile) {
        Map<String, List<Integer>> map = new HashMap<>(16);
        for (Resource resource : allSqlFile) {
            String file = resource.getFilename();
            Map<String, String> tableNameAndVersion = getTableNameAndVersionFromFile(file);
            String tableName = tableNameAndVersion.get("tableName");
            Integer version = Integer.parseInt(tableNameAndVersion.get("version"));
            if (map.get(tableName) == null) {
                List<Integer> list = new ArrayList<>();
                list.add(version);
                map.put(tableName, list);
            } else {
                List<Integer> list = map.get(tableName);
                list.add(version);
                map.put(tableName, list);
            }
        }

        return map;
    }

    private Map<String, String> getTableNameAndVersionFromFile(String file) {
        Map<String, String> map = new HashMap<>(2);
        try {
            file = file.substring(0, file.lastIndexOf(".sql"));
            map.put("tableName", file.substring(0, file.lastIndexOf("_")));
            map.put("version", file.substring(file.lastIndexOf("_") + 1));
            return map;
        } catch (Exception e) {
            throw new RuntimeException("文件名: " + file + ".sql 不符合规范,正确: tableName_version.sql version = Integer ");
        }
    }

    /**
     * 分组出需要更新的表
     *
     * @param updateLogList   日志表数据
     * @param oldGroupFileMap 读取到的sql文件名
     * @return new/old table
     */
    private Map<String, List<TableInfo>> groupTable(List<TableInfo> updateLogList, Map<String, List<Integer>> oldGroupFileMap) {
        List<TableInfo> addTableList = new ArrayList<>();
        List<TableInfo> updateTableList = new ArrayList<>();
        Map<String, List<Integer>> groupFileMap = new HashMap<>(16);
        groupFileMap.putAll(oldGroupFileMap);
        for (TableInfo info : updateLogList) {
            String tableName = info.getTableName();
            // != null -> 日志表已经更新过,这不是第一次
            if (groupFileMap.get(tableName) != null) {
                // 获取所有版本
                List<Integer> versionList = groupFileMap.get(tableName);
                versionList.sort(new Comparator<Integer>() {
                    @Override
                    public int compare(Integer o1, Integer o2) {
                        return o1 - o2;
                    }
                });

                // 获取数据库中最新版本
                Integer tableVersion = info.getTableVersion();
                // 遍历,找到比库中最新版本大的版本,也就是要更新的版本
                for (Integer version : versionList) {
                    if (tableVersion < version) {
                        TableInfo old = new TableInfo();
                        old.setTableName(tableName);
                        old.setTableVersion(version);
                        // 添加
                        updateTableList.add(old);
                    }
                }

                // 将map中删除,map剩下的就是新增的表
                groupFileMap.remove(tableName);

            }
        }

        Set<String> keySet = groupFileMap.keySet();
        for (String s : keySet) {
            List<Integer> versions = groupFileMap.get(s);
            versions.sort(new Comparator<Integer>() {
                @Override
                public int compare(Integer o1, Integer o2) {
                    return o1 - o2;
                }
            });
            for (Integer version : versions) {
                TableInfo newTableInfo = new TableInfo();
                newTableInfo.setTableName(s);
                newTableInfo.setTableVersion(version);
                addTableList.add(newTableInfo);
            }

        }

        Map<String, List<TableInfo>> returnMap = new HashMap<>(2);
        returnMap.put("addTableList", addTableList);
        returnMap.put("updateTableList", updateTableList);

        return returnMap;
    }

    /**
     * 运行sql脚本
     *
     * @param resource sql流文件
     * @throws IOException IOException
     */
    private boolean runSqlScript(Resource resource) throws IOException {
        InputStream inputStream = resource.getInputStream();
        StringBuffer sqlSb = new StringBuffer();
        StringBuilder builder = new StringBuilder();
        byte[] buff = new byte[1024];

        int byteRead = 0;
        while ((byteRead = inputStream.read(buff)) != -1) {
            sqlSb.append(new String(buff, 0, byteRead, StandardCharsets.UTF_8));
        }

        inputStream.close();
        String sqlStr = sqlSb.toString();
        // 去掉大注释
        String[] sqlArr = sqlStr.split("(;\\s*\\r\\n)(;\\s*\\n)");
        for (String s : sqlArr) {
            //去掉小注释
            String sql = s.replaceAll("--.*", "").trim();
            if (!"".equals(sql)) {
                builder.append(sql);
            }
        }

        // 以 英文引号(;)切割 并且单引号里面的英文引号(;)不切割
        return DbUtil.runHandleSql(Arrays.asList(builder.toString().split(";(?=([^\\\']*\\\'[^\\\']*\\\')*[^\\\']*$)")));
    }
}
